﻿using Kingdee.BOS.App.Data;
using Kingdee.BOS;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Util;
using Kingdee.BOS.Core;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;

namespace Ltd
{
    [HotUpdate]
    [Description("补货计划表")]
    public class Replenishment : SysReportBaseService
    {
        /// 初始化事件：在此事件中，设置报表基本属性
        public override void Initialize()
        {
            base.Initialize();
            // 简单账表类型：普通、树形、分页
            this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
            this.ReportProperty.BillKeyFieldName = "FNUMBER";
            // 报表名称
            this.ReportProperty.ReportName = new LocaleValue("补货计划表", base.Context.UserLocale.LCID);
            this.IsCreateTempTableByPlugin = true;
            this.ReportProperty.IsUIDesignerColumns = true;
            this.ReportProperty.IsGroupSummary = true;
            this.ReportProperty.SimpleAllCols = false;
            // 单据主键：两行FID相同，则为同一单的两条分录，单据编号可以不重复显示
            this.ReportProperty.PrimaryKeyFieldName = "FNUMBER";
            this.ReportProperty.IsDefaultOnlyDspSumAndDetailData = true;
            // 报表主键字段名：默认为FIDENTITYID，可以修改
            this.ReportProperty.IdentityFieldName = "FIDENTITYID";
            // 设置精度控制
            //List list = new List();
            //// 数量
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FQty",
            //    DecimalControlFieldName = "FUnitPrecision"
            //});
            //// 单价
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FTAXPRICE",
            //    DecimalControlFieldName = "FPRICEDIGITS"
            //});
            //// 金额
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FALLAMOUNT",
            //    DecimalControlFieldName = "FAMOUNTDIGITS"
            //});
            //this.ReportProperty.DecimalControlFieldList = list;
        }

        public override string GetTableName()
        {
            var result = base.GetTableName();
            return result;
        }

        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            base.BuilderReportSqlAndTempTable(filter, tableName);
            // 拼接过滤条件 ： filter
            // 略
            // 默认排序字段：需要从filter中取用户设置的排序字段
            string seqFld = string.Format(base.KSQL_SEQ, "FNUMBER");

            DateTime FStartDate;
            DateTime FEndDate;
            string FMaterialid = "", FMATERIALID_idsum="";

            DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
            string dyFilter1 = filter.FilterParameter.FilterString;
            string sqlwhereFilter = string.Empty;
            if (!dyFilter1.IsNullOrEmptyOrWhiteSpace())
            {
                dyFilter1 = dyFilter1.Replace("''", "'");
                sqlwhereFilter += string.Format("and {0}", dyFilter1);
            }
            StringBuilder SQLFilter = new StringBuilder();
            StringBuilder SQLFilter1 = new StringBuilder();
            StringBuilder SQLFilter2 = new StringBuilder();
            StringBuilder SQLFilter3 = new StringBuilder();
            //FStartDate = Convert.ToDateTime(dyFilter["FStartDate"]);//开始日期
            //FEndDate = Convert.ToDateTime(dyFilter["FEndDate"]);//最后发货日期
            ////过滤 日期
            //if (!FStartDate.ToString().Contains("0001") && !FEndDate.ToString().Contains("0001"))
            //{
            //    SQLFilter.Append("and t.FDATE>='" + FStartDate + "' and t.FDATE<='" + FEndDate + "'");
            //}
            ////if (!dyFilter.IsNullOrEmpty())
            ////{
            //if (!Convert.ToString(dyFilter["FMaterialid_id"]).Equals("0"))
            //{
            //    SQLFilter1.Append("and t2.FMATERIALID='" + dyFilter["FMaterialid_id"] + "'");
            //}
            //if (!Convert.ToString(dyFilter["FSuppiler_id"]).Equals("0"))
            //{
            //    SQLFilter1.Append("and t6.FSUPPLIERID='" + dyFilter["FSuppiler_id"] + "'");
            //}
            //if (!Convert.ToString(dyFilter["FOrgId_id"]).Equals("0"))
            //{
            //    SQLFilter1.Append("and t.FPURCHASEORGID='" + dyFilter["FOrgId_id"] + "'");
            //}
            //if (!Convert.ToString(dyFilter["FBillNo"]).Equals(""))
            //{
            //    SQLFilter1.Append("and t.FBillNo='" + dyFilter["FBillNo"] + "'");
            //}
            //if (!Convert.ToString(dyFilter1).Equals(""))
            //{
            //    SQLFilter1.Append("and " + dyFilter1.Replace("FBILLNO", "T.FBILLNO") + "");
            //}
            if (!Convert.ToString(dyFilter["FJANCODE"]).Equals(""))
            {
                SQLFilter.Append("and t.F_TRAV_TEXT_TZK LIKE '%" + dyFilter["FJANCODE"] + "%'");
            }
            if (!Convert.ToString(dyFilter["FMaterialGroup"]).Equals(""))
            {
                SQLFilter.Append("and T3.FNAME LIKE '%" + dyFilter["FMaterialGroup"] + "%'");
            }
            DynamicObjectCollection dyCollection1 = dyFilter["FNUMBER"] as DynamicObjectCollection;
            if (dyCollection1.Count > 0)
            {
                foreach (var dobj in dyCollection1)
                {
                    //string FSuppiler_id = Convert.ToString(dobj["FSUPPLIERID_id"]);
                    DynamicObject d = dobj["FNUMBER"] as DynamicObject;
                    string FMATERIALIDNumber = d["Number"].ToString();
                    FMATERIALID_idsum = FMATERIALID_idsum + "'" + FMATERIALIDNumber + "'" + ",";
                }
                SQLFilter.Append("and t.fnumber IN (" + FMATERIALID_idsum.TrimEnd(',') + ")");
            }
            //}

            // 取数SQL
            string sql = string.Format(@"/*dialect*/
-----------------------------------------7天日均出库量-----------------------------------------------------------
SELECT T1.FMATERIALID,SUM(T1.FPRICEQTY)/7 FREALQTY 
INTO #CK 
FROM t_AR_receivable t
left join t_AR_receivableEntry t1 on t.FID=t1.FID
WHERE  T.FDATE >= DATEADD(DAY, -7, GETDATE())
GROUP BY T1.FMATERIALID

-----------------------------------------30天日均出库量-----------------------------------------------------------
SELECT T1.FMATERIALID,SUM(T1.FPRICEQTY)/30 FREALQTY INTO #CK1 FROM t_AR_receivable t
left join t_AR_receivableEntry t1 on t.FID=t1.FID
WHERE  T.FDATE >= DATEADD(DAY, -30, GETDATE())
GROUP BY T1.FMATERIALID

-----------------------------------------在途数量-----------------------------------------------------------
SELECT T1.FMATERIALID,SUM(T2.FPRICEQTY)-SUM(T3.FINSTOCKJOINQTY)ZTQTY INTO #ZT FROM T_PUR_Receive T
LEFT JOIN T_PUR_ReceiveEntry T1 ON T.FID=T1.FID
LEFT JOIN T_PUR_RECEIVEENTRY_F T2 ON T1.FENTRYID=T2.FENTRYID
LEFT JOIN T_PUR_RECEIVEENTRY_S T3 ON T1.FENTRYID=T3.FENTRYID
WHERE T.FDOCUMENTSTATUS='C'
GROUP BY T1.FMATERIALID
-----------------------------------------采购订单数量-----------------------------------------------------------
SELECT T1.FMATERIALID,SUM(T1.FQTY)FQTY,SUM(T2.FRemainReceiveQty)FRemainReceiveQty INTO #CGDD FROM t_PUR_POOrder T
LEFT JOIN t_PUR_POOrderEntry T1 ON T.FID=T1.FID
LEFT JOIN T_PUR_POORDERENTRY_R T2 ON T1.FENTRYID=T2.FENTRYID
WHERE T.FDOCUMENTSTATUS='C'
GROUP BY T1.FMATERIALID

-----------------------------------------销售订单剩余出库数量-----------------------------------------------------------
SELECT t1.FMATERIALID,sum(t1.FQTY)FYYQDSL INTO #YYQDSL FROM T_SAL_ORDER t
left join T_SAL_ORDERENTRY t1 on t.FID=t1.FID
where FBILLTYPEID='6735a112b8d00f' 
group by t1.FMATERIALID

SELECT T.FNUMBER,T1.FNAME,T.F_TRAV_TEXT_TZK,T3.FNAME FGROUP,ISNULL(T.F_TRAV_QTY_QTR1,0)FBASEQTY,
CONVERT(DECIMAL(10, 2),ISNULL(T5.FREALQTY,0))FREALQTY7,
CONVERT(DECIMAL(10, 2),ISNULL(T6.FREALQTY,0))FREALQTY30,
ISNULL(T7.ZTQTY,0)FZTQTY,(ISNULL(T8.FRemainReceiveQty,0))FWFHQTY,T9.FSAFESTOCK,T11.FNAME FCGY,
T12.FYYQDSL,T1.FSPECIFICATION
INTO #TABLE
FROM T_BD_MATERIAL T
LEFT JOIN T_BD_MATERIAL_L T1 ON T.FMATERIALID=T1.FMATERIALID
LEFT JOIN T_BD_MATERIALGROUP T2 ON T.FMATERIALGROUP=T2.FID
LEFT JOIN T_BD_MATERIALGROUP_L T3 ON T2.FID=T3.FID
LEFT JOIN #CK T5 ON T.FMATERIALID=T5.FMATERIALID
LEFT JOIN #CK1 T6 ON T.FMATERIALID=T6.FMATERIALID
LEFT JOIN #ZT T7 ON T.FMATERIALID=T7.FMATERIALID
LEFT JOIN #CGDD T8 ON T.FMATERIALID=T8.FMATERIALID
LEFT JOIN t_BD_MaterialStock T9 ON T.FMATERIALID=T9.FMATERIALID
LEFT JOIN t_bd_MaterialPurchase T10 ON T.FMATERIALID=T10.FMATERIALID
LEFT JOIN V_BD_BUYER_L T11 ON T10.FPURCHASERID=T11.FID
LEFT JOIN #YYQDSL T12 ON T.FMATERIALID=T12.FMATERIALID
WHERE T.FDOCUMENTSTATUS='C' AND T1.FLOCALEID=2052 AND T3.FLOCALEID=2052
{2}

SELECT *,
CASE WHEN FREALQTY30<>0 THEN CEILING(FBASEQTY/+FREALQTY30) ELSE 0 END FXGNQ,
CONVERT(DECIMAL(10, 2),FREALQTY30*90-FWFHQTY-FZTQTY-FBASEQTY)FBHSLYC,
CASE WHEN FREALQTY30<>0 THEN (CEILING(FBASEQTY/+FREALQTY30)+FZTQTY+FWFHQTY)/FREALQTY30 ELSE (FZTQTY+FWFHQTY)/FREALQTY30 END FLJKCQKTS,
CASE WHEN FREALQTY30<>0 THEN FREALQTY30*30-CEILING(FBASEQTY/+FREALQTY30)-FZTQTY-FWFHQTY ELSE FREALQTY30*30-FZTQTY-FWFHQTY END FBHSLYC30,
CASE WHEN FREALQTY30<>0 THEN FREALQTY30*14-CEILING(FBASEQTY/+FREALQTY30)-FZTQTY ELSE FREALQTY30*30-FZTQTY END FBHSLYC14,
{0} into {1}
FROM #TABLE
where FNUMBER is not null {3}
                                        ", seqFld, tableName, SQLFilter, sqlwhereFilter);
            DBUtils.ExecuteDynamicObject(this.Context, sql);
        }

        protected override string GetIdentityFieldIndexSQL(string tableName)
        {
            string result = base.GetIdentityFieldIndexSQL(tableName);
            return result;
        }

        protected override void ExecuteBatch(List<string> listSql)
        {
            base.ExecuteBatch(listSql);
        }

        protected override string GetSummaryColumsSQL(List<SummaryField> summaryFields)
        {
            var result = base.GetSummaryColumsSQL(summaryFields);
            return result;
        }

        protected override System.Data.DataTable GetListData(string sSQL)
        {
            var result = base.GetListData(sSQL);
            return result;
        }

        protected override System.Data.DataTable GetReportData(IRptParams filter)
        {
            var result = base.GetReportData(filter);
            return result;
        }

        protected override System.Data.DataTable GetReportData(string tablename, IRptParams filter)
        {
            var result = base.GetReportData(tablename, filter);
            return result;
        }

        public override int GetRowsCount(IRptParams filter)
        {
            var result = base.GetRowsCount(filter);
            return result;
        }

        protected override string BuilderFromWhereSQL(IRptParams filter)
        {
            string result = base.BuilderFromWhereSQL(filter);
            return result;
        }

        protected override string BuilderSelectFieldSQL(IRptParams filter)
        {
            string result = base.BuilderSelectFieldSQL(filter);
            return result;
        }

        protected override string BuilderTempTableOrderBySQL(IRptParams filter)
        {
            string result = base.BuilderTempTableOrderBySQL(filter);
            return result;
        }

        public override void CloseReport()
        {
            base.CloseReport();
        }

        protected override string CreateGroupSummaryData(IRptParams filter, string tablename)
        {
            string result = base.CreateGroupSummaryData(filter, tablename);
            return result;
        }

        protected override void CreateTempTable(string sSQL)
        {
            base.CreateTempTable(sSQL);
        }

        public override void DropTempTable()
        {
            base.DropTempTable();
        }

        public override System.Data.DataTable GetList(IRptParams filter)
        {
            var result = base.GetList(filter);
            return result;
        }

        public override List<long> GetOrgIdList(IRptParams filter)
        {
            var result = base.GetOrgIdList(filter);
            return result;
        }

        public override List<Kingdee.BOS.Core.Metadata.TreeNode> GetTreeNodes(IRptParams filter)
        {
            var result = base.GetTreeNodes(filter);
            return result;
        }
    }
}
